package com.lw.rfid.tools;

import android.os.Environment;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class DjxlExcel {

    String fullfilename;
    WritableWorkbook wworkbook;
    String befstr = "A0000000000000000000";
    int X, Y;
    WritableSheet Sheet1;

    public DjxlExcel(String filename) {

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
                "yyyyMMdd_HHmmss");// HH:mm:ss
        // 获取当前时间
        Date date = new Date(System.currentTimeMillis());

        fullfilename = "/sdcard/ARFID";
        boolean sdCardExist = Environment.getExternalStorageState().equals(
                android.os.Environment.MEDIA_MOUNTED); // 判断sd卡是否存在
        if (sdCardExist) {
            // fullfilename =
            // Environment.getExternalStorageDirectory().getPath()+
            // File.separator + "ARFID" + File.separator;//获取跟目录
            fullfilename = Environment.getExternalStorageDirectory().getPath()
                    + File.separator;
        }

        File file = new File(fullfilename);
        if (!file.exists())
            file.mkdir();

        fullfilename += filename + "_" + simpleDateFormat.format(date) + ".xls";

    }

    /***
     *
     * @param sheet1
     * @param x
     *            x是矩阵的x轴长度
     * @param y
     *            y是矩阵y轴的下行号
     * @param row
     *            y轴上行号
     * @param sort
     * @param wcf_table_green
     * @param wcf_table_red
     * @param ltags
     * @return
     * @throws RowsExceededException
     * @throws WriteException
     */
    private int square_v1(WritableSheet sheet1, int x, int y, int row,
                          int sort, WritableCellFormat wcf_table_green,
                          WritableCellFormat wcf_table_red, List<String> ltags)
            throws RowsExceededException, WriteException {
        for (int i = 0; i < x; i++) {
            for (int j = row; j < y; j++) {
                int val = sort + (i) * (y - row) + (j - row) + 1;

                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_green));
                else
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_red));

                if (i == x - 1 && j == y - 1)
                    sort = val;
            }
        }

        return sort;
    }

    /***
     *
     * @param sheet1
     * @param x
     *            x是矩阵的x轴长度
     * @param y
     *            y是矩阵y轴的下行号
     * @param row
     *            y轴上行号
     * @param sort
     * @param wcf_table_green
     * @param wcf_table_red
     * @param ltags
     * @return
     * @throws RowsExceededException
     * @throws WriteException
     */
    private int rectangular_v1(WritableSheet sheet1, int x, int y, int row,
                               int sort, WritableCellFormat wcf_table_green,
                               WritableCellFormat wcf_table_red, List<String> ltags)
            throws RowsExceededException, WriteException {
        for (int j = y; j > row; j--) {
            for (int i = 0; i < x; i++) {

                int val = sort + (y - j) * x + i + 1;
                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_green));
                else
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_red));

                if (i == x - 1 && j == row + 1)
                    sort = val;
            }
        }

        x += 12;
        for (int j = y; j > row; j--) {
            for (int i = 12; i < x; i++) {

                int val = sort + (y - j) * (x - 12) + (i - 12) + 1;
                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_green));
                else
                    sheet1.addCell(new Label(i, j, valstr, wcf_table_red));

                if (i == x - 1 && j == row + 1)
                    sort = val;
            }
        }

        return sort;
    }

    public void CreateTestBoxExcelfile_v1(List<String> ltags)
            throws IOException, WriteException {
        // FileOutputStream nfile=new FileOutputStream(fullfilename);
        File nfile = new File(fullfilename);
        // nfile.createNewFile();

        wworkbook = Workbook.createWorkbook(nfile);

        WritableSheet sheet1 = wworkbook.createSheet("sheet1", 0);
        WritableSheet sheet2 = wworkbook.createSheet("sheet2", 1);
        WritableSheet sheet3 = wworkbook.createSheet("sheet3", 2);

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_green = new WritableCellFormat(wf_table);// 单元格定义
        wcf_table_green.setBackground(jxl.format.Colour.GREEN);// 设置单元格的背景颜色
        wcf_table_green.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式

        WritableCellFormat wcf_table_red = new WritableCellFormat(wf_table);// 单元格定义
        wcf_table_red.setBackground(jxl.format.Colour.RED);// 设置单元格的背景颜色
        wcf_table_red.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式
        // **
        // *使用样式的单元格
        // *
        // 1.添加Label对象三个参数意思:【列，行，值】
        // sheet1.addCell(new Label(0,0,"A0000001",wcf_table_green));
        // //普通的带有定义格式的单元格
        // sheet1.addCell(new Label(0,1,"A0000002",wcf_table_red));
        // //普通的带有定义格式的单元格
        // sheet1.mergeCells(0, 0, 5, 0); // 合并单元格

        int row = 0;
        int sort = 0;
        int x = 8, y = 9 + row;

        sort = square_v1(sheet1, x, y, row, sort, wcf_table_green,
                wcf_table_red, ltags);

        for (int k = 0; k < 10; k++) {
            row += 9;
            x = 12;
            y = 3 + row;

            sort = rectangular_v1(sheet1, x, y, row, sort, wcf_table_green,
                    wcf_table_red, ltags);

            row += 3 + 2;
            x = 8;
            y = 9 + row;

            sort = square_v1(sheet1, x, y, row, sort, wcf_table_green,
                    wcf_table_red, ltags);
        }

        // 写入Exel工作表
        wworkbook.write();
        // 关闭Excel工作薄对象
        wworkbook.close();
    }

    /***
     *
     * @param sheet1
     * @param x
     *            列宽 x是矩阵的x轴长度
     * @param y
     *            y是矩阵y轴的下行号
     * @param row
     *            y轴上行号
     * @param sort
     * @param wcf_table_green
     * @param wcf_table_red
     * @param ltags
     * @return
     * @throws RowsExceededException
     * @throws WriteException
     */
    private int square_v2(WritableSheet sheet1, int x, int y, int row,
                          int sort, WritableCellFormat wcf_table_green,
                          WritableCellFormat wcf_table_red, List<String> ltags)
            throws RowsExceededException, WriteException {
        for (int i = 0; i < x; i++) {
            for (int j = row; j < y; j++) {
                int val = sort + (i) * (y - row) + (j - row) + 1;

                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    // sheet1.addCell(new
                    // Label(i,j,String.valueOf(val),wcf_table_green));
                    sheet1.addCell(new Number(i, j, val, wcf_table_green));
                else
                    // sheet1.addCell(new
                    // Label(i,j,String.valueOf(val),wcf_table_red));
                    sheet1.addCell(new Number(i, j, val, wcf_table_red));
                if (i == x - 1 && j == y - 1)
                    sort = val;
            }
        }

        return sort;
    }

    /***
     *
     * @param sheet1
     * @param x
     *            x是矩阵的x轴长度
     * @param row
     *            y轴上行号
     * @param sort
     * @param wcf_table_green
     * @param wcf_table_red
     * @param ltags
     * @return
     * @throws RowsExceededException
     * @throws WriteException
     */
    private int rectangular_v2(WritableSheet sheet1, int x, int row, int sort,
                               WritableCellFormat wcf_table_green,
                               WritableCellFormat wcf_table_red, List<String> ltags)
            throws RowsExceededException, WriteException {
        int x1 = 8, x2 = 12;
        int y = 3;
        for (int j = row + y; j > row; j--) {
            for (int i = x1; i < x + x1; i++) {

                int val = sort + (row + y - j) * x + i - x1 + 1;

                // 合并
                int m2 = row + (j - row) * y - 3;

                sheet1.mergeCells(i, m2, i, m2 + 2);

                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    // sheet1.addCell(new
                    // Label(i,m2,String.valueOf(val),wcf_table_green)
                    sheet1.addCell(new Number(i, m2, val, wcf_table_green));
                else
                    // sheet1.addCell(new
                    // Label(i,m2,String.valueOf(val),wcf_table_red));
                    sheet1.addCell(new Number(i, m2, val, wcf_table_red));

                if (i == x + x1 - 1 && j == row + 1)
                    sort = val;
            }
        }

        x += x2 + x1;
        for (int j = row + y; j > row; j--) {
            for (int i = x1 + x2; i < x; i++) {

                int val = sort + (row + y - j) * (x - x1 - x2) + (i - x1 - x2)
                        + 1;
                // 合并
                int m2 = row + (j - row) * y - 3;

                sheet1.mergeCells(i, m2, i, m2 + 2);

                String valstr = befstr + String.format("%04d", val);
                if (ltags.contains(valstr))
                    // sheet1.addCell(new
                    // Label(i,m2,String.valueOf(val),wcf_table_green));
                    sheet1.addCell(new Number(i, m2, val, wcf_table_green));
                else
                    // sheet1.addCell(new
                    // Label(i,m2,String.valueOf(val),wcf_table_red));
                    sheet1.addCell(new Number(i, m2, val, wcf_table_red));

                if (i == x - 1 && j == row + 1)
                    sort = val;
            }
        }

        return sort;
    }

    public void CreateTestBoxExcelfile_v2(List<String> ltags)
            throws IOException, WriteException {
        // FileOutputStream nfile=new FileOutputStream(fullfilename);
        File nfile = new File(fullfilename);
        // nfile.createNewFile();

        wworkbook = Workbook.createWorkbook(nfile);

        WritableSheet sheet1 = wworkbook.createSheet("sheet1", 0);
        WritableSheet sheet2 = wworkbook.createSheet("sheet2", 1);
        WritableSheet sheet3 = wworkbook.createSheet("sheet3", 2);

        int rowhigh = 0;
        int font_l = 0;
        int font_r = 0;
        int col_l = 0;
        int col_r = 0;
        boolean isVprintf = true;

        if (isVprintf) {
            // 竖起打印
            rowhigh = 215;
            font_l = 7;
            font_r = 6;
            col_l = 4;
            col_r = 2;
        } else {
            font_l = 10;
            font_r = 7;
            col_l = 7;
            col_r = 3;
        }

        if (isVprintf) {
            for (int i = 0; i < 141; i++)
                sheet1.setRowView(i, rowhigh, false); // 设置行高
        }

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, font_l,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableFont wf_table_s = new WritableFont(WritableFont.ARIAL, font_r,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_l = new WritableCellFormat(wf_table);// 单元格定义
        wcf_table_l.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_table_l.setAlignment(Alignment.CENTRE);// 设置对齐方式
        wcf_table_l.setBorder(Border.ALL, BorderLineStyle.THIN);

        WritableCellFormat wcf_table_l_no = new WritableCellFormat(wf_table);// 单元格定义

        // Color color = Color.decode("#FAEBD7"); // 自定义的颜色

        wworkbook.setColourRGB(Colour.RED, 250, 235, 215);
        wcf_table_l_no.setBackground(Colour.RED);// 设置单元格的背景颜色
        wcf_table_l_no.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_table_l_no.setAlignment(Alignment.CENTRE);// 设置对齐方式
        wcf_table_l_no.setBorder(Border.ALL, BorderLineStyle.THIN);

        WritableCellFormat wcf_table_r = new WritableCellFormat(wf_table_s);// 单元格定义
        wcf_table_r.setAlignment(Alignment.CENTRE);// 设置对齐方式
        wcf_table_r.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_table_r.setOrientation(Orientation.VERTICAL);
        wcf_table_r.setBorder(Border.ALL, BorderLineStyle.THIN);

        WritableCellFormat wcf_table_r_no = new WritableCellFormat(wf_table_s);// 单元格定义
        wcf_table_r_no.setBackground(Colour.RED);// 设置单元格的背景颜色
        wcf_table_r_no.setAlignment(Alignment.CENTRE);// 设置对齐方式
        wcf_table_r_no.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_table_r_no.setOrientation(Orientation.VERTICAL);

        wcf_table_r_no.setBorder(Border.ALL, BorderLineStyle.THIN);

        int col = 0;
        int row = 0;
        int page = 1;
        int sort = 0;
        // 设置列宽
        for (int i = 0; i < 8; i++)
            sheet1.setColumnView(i, col_l); // 设置col显示样式

        for (int i = 8; i < 32; i++)
            sheet1.setColumnView(i, col_r); // 设置col显示样式

        sheet1.mergeCells(col, row, col + 32 - 1, row); // 合并单元格
        Label lbh = new Label(0, 0, "", wcf_table_l);

        WritableCellFormat cellFormat2 = new WritableCellFormat();
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        lbh.setCellFormat(cellFormat2);
        sheet1.addCell(lbh); // 普通的带有定义格式的单元格

        row++;

        int x = 8;// 列宽
        int y = 9;// 行数
        int x1 = 12;// 列宽
        for (int k = 0; k < 11; k++) {
            sheet1.mergeCells(col, row, col + 8 - 1, row); // 合并单元格

            Label lbl = new Label(col, row, "第" + page++ + "层",
                    wcf_table_l);
            // lbl.setCellFormat(cellFormat2);
            sheet1.addCell(lbl); // 普通的带有定义格式的单元格

            if (k < 10) {
                sheet1.mergeCells(col + 8, row, col + 32 - 1, row); // 合并单元格
                Label lbr = new Label(col + 8, row, "第"
                        + page++ + "层", wcf_table_l);
                // lbr.setCellFormat(cellFormat2);
                sheet1.addCell(lbr);
            }

            row++;

            sort = square_v2(sheet1, x, row + y, row, sort, wcf_table_l,
                    wcf_table_l_no, ltags);

            if (k < 10)
                sort = rectangular_v2(sheet1, x1, row, sort, wcf_table_r,
                        wcf_table_r_no, ltags);

            if (isVprintf)
                row += 9 + 1;
            else
                row += 9 + 2;

        }
        // 写入Exel工作表
        wworkbook.write();
        // 关闭Excel工作薄对象
        wworkbook.close();
    }

    public void CreatereturnlossExcelfile(List<String[]> listr)
            throws IOException, WriteException {
        File nfile = new File(fullfilename);
        // nfile.createNewFile();

        wworkbook = Workbook.createWorkbook(nfile);

        WritableSheet sheet1 = wworkbook.createSheet("sheet1", 0);
        WritableSheet sheet2 = wworkbook.createSheet("sheet2", 1);
        WritableSheet sheet3 = wworkbook.createSheet("sheet3", 2);

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_green = new WritableCellFormat(wf_table);// 单元格定义
        // wcf_table_green.setBackground(jxl.format.Colour.GREEN);//设置单元格的背景颜色
        wcf_table_green.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式

        WritableCellFormat wcf_table_red = new WritableCellFormat(wf_table);// 单元格定义
        wcf_table_red.setBackground(jxl.format.Colour.RED);// 设置单元格的背景颜色
        wcf_table_red.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式

        int row = 0;
        int sort = 0;
        int x = 0, y = 0;

        sheet1.addCell(new Label(x++, y, "频率", wcf_table_green));
        sheet1.addCell(new Label(x++, y, "RL(dB)", wcf_table_green));
        sheet1.addCell(new Label(x++, y, "VRWR", wcf_table_green));
        sheet1.addCell(new Label(x++, y, "天线", wcf_table_green));

        for (int i = 0; i < listr.size(); i++) {
            x = 0;
            y++;
            String[] stray = listr.get(i);
            for (int j = 0; j < stray.length; j++) {
                sheet1.addCell(new Label(x++, y, stray[j], wcf_table_green));
            }
        }

        // 写入Exel工作表
        wworkbook.write();
        // 关闭Excel工作薄对象
        wworkbook.close();
    }

    public void CreatereExcelfile(List<String> headlistr) throws IOException,
            WriteException {
        File nfile = new File(fullfilename);
        // nfile.createNewFile();

        wworkbook = Workbook.createWorkbook(nfile);
        X = Y = 0;

        Sheet1 = wworkbook.createSheet("sheet1", 0);

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_green = new WritableCellFormat(wf_table);// 单元格定义
        // wcf_table_green.setBackground(jxl.format.Colour.GREEN);//设置单元格的背景颜色
        wcf_table_green.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式

        int row = 0;
        int sort = 0;
        int x = 0, y = 0;

        for (int i = 0; i < headlistr.size(); i++)
            Sheet1.addCell(new Label(x++, y, headlistr.get(i), wcf_table_green));
        Y++;

        // 写入Exel工作表
        // wworkbook.write();

    }

    public void CreatereExcelfile(String head, List<String> headlistr)
            throws IOException, WriteException {
        File nfile = new File(fullfilename);
        // nfile.createNewFile();

        wworkbook = Workbook.createWorkbook(nfile);
        X = Y = 0;

        Sheet1 = wworkbook.createSheet("sheet1", 0);

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_green = new WritableCellFormat(wf_table);// 单元格定义
        // wcf_table_green.setBackground(jxl.format.Colour.GREEN);//设置单元格的背景颜色
        wcf_table_green.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式

        int row = 0;
        int sort = 0;
        int x = 0, y = 0;

        Sheet1.mergeCells(0, 0, headlistr.size(), 0);

        Label lbh = new Label(0, Y, head, wcf_table_green);
        Sheet1.addCell(lbh);
        Y++;
        for (int i = 0; i < headlistr.size(); i++)
            Sheet1.addCell(new Label(x++, Y, headlistr.get(i), wcf_table_green));
        Y++;

        // 写入Exel工作表
        // wworkbook.write();

    }

    public void WriteExcelfile(Object[] objes) throws IOException,
            WriteException {

        WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

        WritableCellFormat wcf_table_green = new WritableCellFormat(wf_table);// 单元格定义
        // wcf_table_green.setBackground(jxl.format.Colour.GREEN);//设置单元格的背景颜色
        wcf_table_green.setAlignment(jxl.format.Alignment.CENTRE);// 设置对齐方式
        int x = 0;
        for (int i = 0; i < objes.length; i++) {
            if (objes[i] instanceof String)
                Sheet1.addCell(new Label(x++, Y, (String) objes[i],
                        wcf_table_green));
            else if (objes[i] instanceof Integer)
                Sheet1.addCell(new Number(x++, Y, (Integer) objes[i],
                        wcf_table_green));
            else if (objes[i] instanceof Long)
                Sheet1.addCell(new Number(x++, Y, (Long) objes[i],
                        wcf_table_green));
            else if (objes[i] instanceof Float)
                Sheet1.addCell(new Number(x++, Y, (Float) objes[i],
                        wcf_table_green));
            else if (objes[i] instanceof Double)
                Sheet1.addCell(new Number(x++, Y, (Double) objes[i],
                        wcf_table_green));
        }
        Y++;

    }

    public void Addline(int l) {
        Y += l;
    }

    public int GetY() {
        return Y;
    }

    public void mergeandtext(int x1, int y1, int x2, int y2, String msg) {
        try {
            Sheet1.mergeCells(x1, y1, x2, y2);
            WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    jxl.format.Colour.BLACK);// 定义格式 字体 下划线 斜体 粗体 颜色

            WritableCellFormat wcf_table_l = new WritableCellFormat(wf_table);// 单元格定义
            wcf_table_l.setVerticalAlignment(VerticalAlignment.CENTRE);
            wcf_table_l.setAlignment(Alignment.CENTRE);// 设置对齐方式
            Label lbh = new Label(x1, Y, msg, wcf_table_l);
            Sheet1.addCell(lbh);
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } // 合并单元格
    }

    public void SaveandCloseExcelfile() throws IOException, WriteException {
        // 写入Exel工作表
        wworkbook.write();
        // 关闭Excel工作薄对象
        wworkbook.close();
    }

}
